/**
 * 获取带标签的工具列表API
 * 从数据库中查询所有工具及其关联的标签信息
 */

import type { ToolItem, ToolList } from '~/types/tools'
import { createDatabaseConnection } from '../config/database'

/**
 * 处理获取带标签工具列表的请求
 * 执行多表联查，获取工具及其标签信息
 */
export default defineEventHandler(async (event) => {
  try {
    // 获取POST请求体中的JSON数据
    const body = await readBody(event)
    const { categoryIds = [], pageSize = 10, pageNum = 1, all = false } = body || {}

    if (!categoryIds || categoryIds.length === 0) {
      throw createError({
        statusCode: 400,
        statusMessage: '分类ID数组不能为空'
      })
    }

    // 创建数据库连接（生产环境应使用连接池和环境变量）
    const connection = await createDatabaseConnection()

    // 第一步：查询所有指定分类的名称
    const categorySql = `
      SELECT tab_id, title, description
      FROM ai_tools_tab
      WHERE tab_id IN (${categoryIds.map(() => '?').join(',')})
    `
    // eslint-disable-next-line @typescript-eslint/ban-ts-comment
    // @ts-expect-error
    const [categoryResult] = await connection.execute(categorySql, categoryIds)
    
    if (!categoryResult || (categoryResult as { tab_id: number; title: string }[]).length === 0) {
      throw createError({
        statusCode: 404,
        statusMessage: '分类不存在'
      })
    }

    // 构建分类id到名称的映射
    const categoryMap = new Map<number, {
      id: number
      title: string
      description: string
    }>()
    ;(categoryResult as { tab_id: number; title: string; description: string }[]).forEach(cat => {
      categoryMap.set(cat.tab_id, {
        id: cat.tab_id,
        title: cat.title,
        description: cat.description
      })
    })

    // 第二步：为每个分类查询工具列表（带标签和分类信息）
    const categoryToolsMap = new Map<number, ToolItem[]>()
    const categoryTotalsMap = new Map<number, number>()
    
    // 遍历每个分类，分别查询和分页
    for (const categoryId of categoryIds) {
      // 查询该分类下的工具总数
      const countSql = `
        SELECT COUNT(*) as total
        FROM ai_tools
        WHERE tab_id = ?
      `
      // eslint-disable-next-line @typescript-eslint/ban-ts-comment
      // @ts-expect-error
      const [countResult] = await connection.execute(countSql, [categoryId])
      const total = (countResult as { total: number }[])[0].total
      categoryTotalsMap.set(categoryId, total)

      // 查询该分类下的工具列表（带分页）
      let toolsSql = `
        SELECT
          at.tool_id,
          at.title as tool_name,
          at.tab_id as category_id,
          at.local_logo_path,
          at.url,
          at.description,
          at.view_hot_score,
          GROUP_CONCAT(t.tag_name) as tag_names
        FROM ai_tools at
        LEFT JOIN ai_tool_tag_rel atr ON at.tool_id = atr.tool_id
        LEFT JOIN ai_tag t ON atr.tag_id = t.id
        WHERE at.tab_id = ?
        GROUP BY at.tool_id, at.tab_id
      `
      
      // 如果all参数为false，则添加分页限制
      if (!all) {
        const offset = (pageNum - 1) * pageSize
        toolsSql += ` LIMIT ${offset}, ${pageSize}`
      }
      
      // eslint-disable-next-line @typescript-eslint/ban-ts-comment
      // @ts-expect-error
      const [toolsResult] = await connection.execute(toolsSql, [categoryId])

      type SqlToolsResult = {
        tool_id: number
        view_hot_score: number
        tool_name: string
        category_id: number
        local_logo_path: string
        url: string
        description: string
        tag_names: string | null
      }
      
      // 处理该分类的工具数据
      const tools: ToolItem[] = (toolsResult as SqlToolsResult[]).map(row => ({
        toolId: row.tool_id,
        toolName: row.tool_name,
        localLogoPath: row.local_logo_path,
        url: row.url,
        description: row.description,
        tags: row.tag_names ? row.tag_names.split(',') : [],
        categoryId: row.category_id,
        viewHotScore: row.view_hot_score
      }))
      
      categoryToolsMap.set(categoryId, tools)
    }
    // 关闭数据库连接
    await connection.end()

    // 构建分组数据格式
    const groupedData: ToolList[] = Array.from(categoryMap.entries()).map(([categoryId, info]) => ({
      title: info.title,
      description: info.description,
      list: categoryToolsMap.get(categoryId) || [],
      total: categoryTotalsMap.get(categoryId) || 0,
      categoryId: categoryId
    }))

    // 计算总工具数（所有分类的总和）
    const totalTools = Array.from(categoryTotalsMap.values()).reduce((sum, count) => sum + count, 0)

    // 返回成功响应
    return {
      success: true,
      data: groupedData,
      total: totalTools,
      pageSize: all ? totalTools : pageSize, // 如果查询所有数据，pageSize设为总数
      pageNum: pageNum,
      all: all // 返回是否查询了所有数据的标识
    }
  } catch (error) {
    // 错误处理：记录错误日志并返回500错误
    console.error('Database query error:', error)
    throw createError({
      statusCode: 500,
      statusMessage: '数据库查询失败'
    })
  }
})